from lib.http import Response, Request
from submodules.common.lib.datetime_ import get_timestamp_unix_ms
from submodules.common.lib.log_ import logger

from tools import mysql_

db_name = "house_information"
table_name = "user_tmp"
column_names = ["id", "f_name", "age", "create_time"]
maping_column_names_to_params = {
    "id": "id",
    "f_name": "f_name",
    "age": "age",
    "create_time": "create_time"
}


# 增加
def user_tmp_create(req):
    try:
        necessary_param_list = ["f_name", "age"]
        res = Response()
        body = req.body

        # body格式校验
        if isinstance(body, dict):
            pass
        else:
            res.code = 1002
            res.msg = f"body格式不正确！"
            logger.error(res.msg)
            return res.__dict__

        # 参数校验
        for param in necessary_param_list:
            if param in body.keys():
                pass
            else:
                res.code = 1002
                res.msg = f"{param}缺失！"
                logger.error(f"{param}缺失！")
                return res.__dict__

        key_list, value_list = [], []
        for k, v in body.items():
            key_list.append(k)
            value_list.append(v)
        value_list_with_quotes = ['"' + str(x) + '"' for x in value_list]
        timestamp_unix = get_timestamp_unix_ms()
        sql = f'''INSERT INTO {db_name}.{table_name} (id,create_time,{",".join(key_list)}) VALUES (DEFAULT, {timestamp_unix},{",".join(value_list_with_quotes)});'''
        logger.info(sql)
        mysql_.insert(sql)
        res.code = 20000
        res.msg = "success"
        return res.__dict__
    except Exception as e:
        res.code = 1001
        res.msg = f"{e.__repr__()}"
        logger.error(res.msg)
        return res.__dict__


# 查询
def select_user_tmp(data_):
    optional_param_list = ["f_name", "age"]
    for param in data_.keys():
        if param in optional_param_list:
            pass
        else:
            logger(f"{param}不可用作查询条件！")
            raise Exception

    optional_sql_str = "WHERE "
    for k, v in data_.items():
        if v != "":
            if k == "f_name":
                optional_sql_str += f"{k} like '%{v}%' and "
            elif k == "age":
                optional_sql_str += f"{k}={v} and"
    if optional_sql_str == "WHERE ":
        optional_sql_str = ""
    else:
        optional_sql_str = optional_sql_str[:-4]

    sql = f'''SELECT * FROM {db_name}.{table_name} {optional_sql_str};'''
    logger(sql)
    res = mysql_.select(sql)
    return res


# 修改
def update_user_tmp(id, data_):
    optional_param_list = ["f_name", "age"]

    for param in data_.keys():
        if param in optional_param_list:
            pass
        else:
            logger(f"{param}不存在或不可修改！")
            raise Exception

    optional_sql_str_list = []
    for k, v in data_.items():
        if v != "":
            optional_sql_str_list.append(f"{k}='{v}'")

    sql = f'''UPDATE {db_name}.{table_name} SET {",".join(optional_sql_str_list)} where id = {id};'''
    logger.info(sql)
    res = mysql_.update(sql)
    return res


# 删除
def delete_user_tmp(data_):
    necessary_param_list = ["id"]
    # 参数校验
    for param in necessary_param_list:
        if param in data_.keys():
            pass
        else:
            logger(f"{param}缺失！")
            raise Exception
    id = str(data_["id"])
    sql = f'''DELETE FROM {db_name}.{table_name} WHERE id={data_["id"]};'''
    res = mysql_.delete(sql)
    return res


if __name__ == "__main__":
    def test_user_tmp_create():
        req = Request()
        req.body = {
            "f_name": "han",
            "age": "18"
        }
        user_tmp_create(req)


    def test_select_user_tmp():
        d = {
            "f_name": "han",
            "age": "18"
        }
        res = select_user_tmp(d)
        print(res)


    def test_delete_user_tmp():
        d = {
            "id": "7"
        }
        res = delete_user_tmp(d)
        print(res)


    def test_update_user_tmp():
        d = {
            "f_name": "han",
            "age": "11",
        }
        id = 6
        res = update_user_tmp(id, d)
        print(res)


    test_user_tmp_create()
